3.07. Процедуры SQL

В РАЗРАБОТКЕНЕ ДЛЯ НОВИЧКОВНЕ ОБЯЗАТЕЛЬНО

Разработчику Аналитику Тестировщику
Архитектору Инженеру

Процедуры SQL

О процедурах

В контексте SQL и реляционных баз данных термин «процедура» почти всегда означает хранимую процедуру (stored procedure) — программный объект, хранящийся в каталоге СУБД и выполняемый на сервере. Понятия «нехранимой процедуры» в классическом смысле не существует, поскольку ключевая характеристика процедуры в СУБД — её сохранение в базе данных для повторного использования. Временные или динамические блоки кода, не сохранённые в виде объекта БД, не считаются процедурами. Хранимые процедуры (Stored Procedures) – мощный инструмент SQL для для создания предопределённых операций, которые выполняются непосредственно на сервере базы данных.

Хранимые процедуры можно классифицировать по нескольким признакам:

По типу реализации
- SQL-процедуры — написаны на диалекте SQL (PL/pgSQL, T-SQL, PL/SQL и т.д.), используют стандартные SQL-операторы и расширения (циклы, условия).
- Процедуры на языках общего назначения — в некоторых СУБД (например, SQL Server с CLR, PostgreSQL с функциями на C/Python) допускается реализация на внешних языках.
По способу вызова
- Именованные процедуры — создаются с помощью CREATE PROCEDURE и вызываются по имени.
- Анонимные блоки — в PL/SQL (Oracle) поддерживаются блоки без имени, но они не являются «процедурами» в строгом смысле.
По уровню доступа
- Системные процедуры — предоставляемые СУБД для администрирования (например, sp_help, sp_rename в SQL Server).
- Пользовательские процедуры — созданные разработчиком под конкретную логику.
По параметрам. Процедуры могут принимать:
- Входные параметры (IN)
- Выходные (OUT)
- Входные и выходные (INOUT / IN OUT)

Пример (PostgreSQL):

CREATE PROCEDURE transfer_funds(
    IN from_account INT,
    IN to_account INT,
    IN amount DECIMAL,
    OUT status TEXT
)
AS $$
BEGIN
    -- Логика перевода
    UPDATE accounts SET balance = balance - amount WHERE id = from_account;
    UPDATE accounts SET balance = balance + amount WHERE id = to_account;
    status := 'OK';
END;
$$ LANGUAGE plpgsql;

★ Хранимая процедура – это набор SQL-операций, сохранённый в БД, именованный блок кода, который можно вызывать по имени. Это объект БД, который может принимать параметры и возвращать результаты, аналог функций в языках программирования, но работающий внутри СУБД.

Хранимые процедуры нужны для следующих целей:

повторное использование кода – один раз написал, много раз вызвал;
безопасность – можно давать права на выполнение процедуры без доступа к таблицам;
производительность – выполняются на сервере, минимизация сетевого трафика;
централизованная логика – изменение в одном месте влияет на все приложения;
сокращение SQL-инъекций – параметры передаются безопасно.

Общая структура процедур:

CREATE [OR REPLACE] PROCEDURE procedure_name ([параметры])
[характеристики]
BEGIN
    -- SQL-операции
END;

Пример:

DELIMITER //
CREATE PROCEDURE GetEmployee(IN emp_id INT)
BEGIN
    SELECT * FROM employees WHERE id = emp_id;
END //
DELIMITER ;

Параметры – значения, которые принимаются процедурами:

Тип параметра	Описание	Пример
IN	Входной параметр (по умолчанию)	IN emp_name VARCHAR(100)
OUT	Выходной параметр (возвращает значение)	OUT total_employees INT
INOUT	Входной и выходной одновременно	INOUT counter INT

Пример с параметрами:

CREATE PROCEDURE UpdateSalary(
    IN emp_id INT,
    IN increase_amount DECIMAL(10,2),
    OUT new_salary DECIMAL(10,2)
BEGIN
    UPDATE employees 
    SET salary = salary + increase_amount 
    WHERE id = emp_id;
    
    SELECT salary INTO new_salary FROM employees WHERE id = emp_id;
END;

Вызов процедур – обращение к уже существующей процедуре, с целью получить результат. Синтаксис вызова зависит от СУБД:

MySQL:

CALL GetEmployee(123);

SQL Server:

EXEC GetEmployee @emp_id = 123;

Oracle:

BEGIN
    GetEmployee(123);
END;

Вызов с выходными параметрами:

-- MySQL
CALL UpdateSalary(123, 5000, @new_salary);
SELECT @new_salary;

-- SQL Server
DECLARE @result DECIMAL(10,2);
EXEC UpdateSalary @emp_id=123, @increase_amount=5000, @new_salary=@result OUTPUT;
SELECT @result;

Таким образом, хранимая процедура имеет три ключевых особенности: упрощение кода, безопасность и производительность.

Для выполнения хранимой процедуры вызывается команда EXEC или EXECUTE.

Отличие от функций

Несмотря на схожесть (оба — хранимые программные объекты), между процедурами и функциями есть принципиальные различия:

Характеристика	Процедура	Функция
Возвращаемое значение	Не обязана возвращать значение. Может использовать `OUT`-параметры.	Должна возвращать одно значение (скалярное, таблицу и т.д.).
Вызов в выражениях	Нельзя вызывать внутри `SELECT`, `WHERE`.	Можно вызывать в `SELECT`, если не модифицирует данные.
Оператор `RETURN`	Может использоваться для раннего выхода, но не возвращает выражение.	Используется для возврата значения: `RETURN expr;`
Модификация данных	Разрешена.	В большинстве СУБД запрещена в контексте `SELECT` (например, в PostgreSQL скалярные функции не должны изменять состояние).
Использование в DML	Может содержать `COMMIT`, `ROLLBACK` (в некоторых СУБД).	Обычно не может управлять транзакциями.
Табличные возвращаемые значения	Поддерживается не везде (например, в SQL Server — да, через `OUTPUT`).	Часто используется: табличные функции (`RETURNS TABLE`) работают как виртуальные таблицы.

Пример функции с возвращаемым значением:

CREATE FUNCTION get_employee_count(dept_id INT) 
RETURNS INT
AS $$
DECLARE cnt INT;
BEGIN
    SELECT COUNT(*) INTO cnt FROM employees WHERE department_id = dept_id;
    RETURN cnt;
END;
$$ LANGUAGE plpgsql;

-- Использование в запросе:
SELECT get_employee_count(5);

Функции используются для вычислений и преобразований, особенно когда результат нужен в рамках SQL-запроса. Процедуры подходят для выполнения сложных бизнес-операций: пакетная обработка, транзакции, вызов нескольких операторов, работа с курсорами.

Триггеры

★ Триггер – специальный вид хранимой процедуры, который автоматически выполняется при наступлении определённого события в базе данных. То есть, когда выполняется INSERT, UPDATE, DELETE в какой-то таблице, триггер может автоматически сделать что-то ещё.

Примеры использования – логирование изменений, каскадные обновления и удаления, обновление связанных таблиц, валидация данных до их вставки, автоматическое обновление поля.

Структура триггера включает:

Событие – когда запускается триггер (INSERT, UPDATE, DELETE);
Таблица – к какой таблице относится;
Функция / тело – какие действия выполняются;
Момент – до или после события (BEFORE / AFTER).

Пример триггера в PostgreSQL:

CREATE OR REPLACE FUNCTION log_user_update()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO user_log(user_id, action, change_time)
    VALUES (OLD.id, 'update', NOW());
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_user_update
AFTER UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION log_user_update();

★ Виды триггеров:

Вид	Когда вызывается	На что реагирует
BEFORE INSERT	До вставки новой строки	Может изменить данные перед вставкой
AFTER INSERT	После вставки	Для обновления другой таблицы
BEFORE UPDATE	Перед обновлением	Можно изменить новые значения
AFTER UPDATE	После обновления	Логирование, синхронизация
BEFORE DELETE	Перед удалением	Может предотвратить данные
AFTER DELETE	После удаления	Чистка, каскадное удаление

Триггеры весьма непростой инструмент, и часто может замедлить операции.

Операторы управления потоком в SQL-процедурах

Оператор	Синтаксис	Описание	СУБД
`DECLARE`	`DECLARE var_name TYPE [DEFAULT value]`	Объявляет переменную	MySQL, SQL Server, PostgreSQL, Oracle
`SET`	`SET var_name = value`	Присваивает значение переменной	Все основные СУБД
`IF-THEN-ELSE`	`IF condition THEN statements [ELSEIF condition THEN statements] [ELSE statements] END IF;`	Условное выполнение кода	Все основные СУБД
`CASE`	`CASE WHEN condition THEN statements [WHEN condition THEN statements] [ELSE statements] END CASE;`	Множественное ветвление	Все основные СУБД
`WHILE`	`WHILE condition DO statements END WHILE;`	Цикл с предусловием	MySQL, SQL Server, PostgreSQL
`REPEAT`	`REPEAT statements UNTIL condition END REPEAT;`	Цикл с постусловием	MySQL, PostgreSQL
`LOOP`	`LOOP statements [LEAVE label;] [ITERATE label;] END LOOP;`	Бесконечный цикл с ручным управлением	MySQL, PostgreSQL
`LEAVE`	`LEAVE label;`	Выход из цикла или блока (аналог break)	MySQL, PostgreSQL
`ITERATE`	`ITERATE label;`	Переход к следующей итерации цикла (аналог continue)	MySQL, PostgreSQL
`FOR`	`FOR var_name IN [REVERSE] start..end [BY step] LOOP statements END LOOP;`	Цикл с счётчиком	PostgreSQL, Oracle
`TRY-CATCH`	`BEGIN TRY statements END TRY BEGIN CATCH statements END CATCH`	Обработка ошибок	SQL Server
`DECLARE HANDLER`	`DECLARE handler_type HANDLER FOR condition handler_statements`	Обработка исключений	MySQL
`RETURN`	`RETURN value;`	Возврат значения из функции	Все основные СУБД
`GOTO`	`GOTO label;`	Безусловный переход	SQL Server, Oracle

Процедуры SQL​

О процедурах​

Отличие от функций​

Триггеры​

Операторы управления потоком в SQL-процедурах​

Процедуры SQL

О процедурах

Отличие от функций

Триггеры

Операторы управления потоком в SQL-процедурах